﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.HSSF.Util;
using System.IO;
using Facade.Excel;

namespace PeisongWeb.admin
{
    public partial class reportSales : adminPage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (hasPermission("EB", 1))
                {
                    drpBind();
                    rptBind();
                }
                else
                {
                    Library.Script.ClientMsgUrl("没有权限！", "index.aspx");
                    return;
                }
                
            }
        }

        private void drpBind()
        {
            DataTable dt = logic.category.getByParent(0);
            drp_CategoryRoot.DataSource = dt;
            drp_CategoryRoot.DataBind();
            drp_CategoryRoot.Items.Insert(0, new System.Web.UI.WebControls.ListItem("--所有大类--", ""));

            ddlCategoryRoot.DataSource = dt;
            ddlCategoryRoot.DataBind();
            ddlCategoryRoot.Items.Insert(0, new ListItem("-所有大类-",""));

            drp_Company.DataSource = logic.company.listWithCompanyType("2");
            drp_Company.DataBind();
            drp_Company.Items.Insert(0, new System.Web.UI.WebControls.ListItem("--所有企业--", "0"));
        }

        void rptBind()
        {
            StringBuilder where = new StringBuilder();
            where.Append("1=1 ");
            if (drp_CategoryRoot.SelectedValue.ToString().Trim() != "")
                where.AppendFormat(" and parentId={0}", drp_CategoryRoot.SelectedValue.ToString().Trim());
            if (drp_CategorySub.SelectedValue.ToString().Trim() != "")
                where.AppendFormat(" and categoryId={0}", drp_CategorySub.SelectedValue.ToString().Trim());
            if (ext_StartInputDt.SelectedDate != DateTime.MinValue)
                where.AppendFormat(" and datediff(d,[inputDt],'{0}')<=0", ext_StartInputDt.SelectedDate);
            if (ext_EndInputDt.SelectedDate != DateTime.MinValue)
                where.AppendFormat(" and datediff(d,[inputDt],'{0}')>=0", ext_EndInputDt.SelectedDate);
            if (drp_Company.SelectedValue.ToString() != "0")
                where.AppendFormat(" and companyId = {0}", Convert.ToInt32(drp_Company.SelectedValue));

            DataTable dt = logic.orderItem.listWithSaleReport(where.ToString());
            rpt_list.DataSource = dt;
            rpt_list.DataBind();

        }
        
        protected void lbtn_Search_Click(object sender, EventArgs e)
        {
            rptBind();
        }

        void categorySubBind()
        {
            if (drp_CategoryRoot.SelectedValue != "")
            {
                DataTable dt = logic.category.getByParent(Convert.ToInt32(drp_CategoryRoot.SelectedValue));
                drp_CategorySub.DataSource = dt;
                drp_CategorySub.DataBind();
                drp_CategorySub.Items.Insert(0, new System.Web.UI.WebControls.ListItem("--所有小类--", ""));
            }
            else
            {
                drp_CategorySub.Items.Clear();
            }
        }
        protected void ddl_CategoryRoot_SelectedIndexChanged(object sender, EventArgs e)
        {
            categorySubBind();
        }
        protected void rpt_list_ItemCommand(object source, RepeaterCommandEventArgs e)
        {
            if (e.CommandName == "details")
            {
                hid_categoryId.Value = e.CommandArgument.ToString();
                txt_startOrderDt.SelectedDate = ext_StartInputDt.SelectedDate;
                txt_endOrderDt.SelectedDate = ext_EndInputDt.SelectedDate;
                txt_orderNo.Text = "";
                AspNetPager2.CurrentPageIndex = 1;

                rptDetailsBind();
                category_details.Show();
            }
        }

        void rptDetailsBind()
        {
            int categoryid = Convert.ToInt32(hid_categoryId.Value);
            int companyId = 0;
            int.TryParse(drp_Company.SelectedValue,out companyId);

            StringBuilder where = new StringBuilder();
            where.Append("categoryId=" + categoryid);
            if (txt_orderNo.Text.ToString().Trim() != "")
                where.AppendFormat(" and orderno = '{0}'", txt_orderNo.Text.ToString().Trim());
            if (txt_startOrderDt.SelectedDate != DateTime.MinValue)
                where.AppendFormat(" and datediff(d,[inputDt],'{0}')<=0", txt_startOrderDt.SelectedDate);
            if (txt_endOrderDt.SelectedDate!=DateTime.MinValue)
                where.AppendFormat(" and datediff(d,[inputDt],'{0}')>=0", txt_endOrderDt.SelectedDate);
            if (companyId != 0)
                where.AppendFormat(" and companyId={0}",companyId);

            DataSet ds = logic.orderItem.listByCategory(where.ToString(), "oiid", AspNetPager2.PageSize, AspNetPager2.CurrentPageIndex);

            if (ds.Tables[0].Rows.Count > 0)
            {
                AspNetPager2.RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }

            rpt_Products.DataSource = ds.Tables[1];
            rpt_Products.DataBind();

            if (ds.Tables[1].Rows.Count > 0)
            {
                category_details.Title = ds.Tables[1].Rows[0]["subCategory"].ToString() + "的详情信息";
            }

        }
        protected void lbn_search_Click(object sender, EventArgs e)
        {
            rptDetailsBind();
        }
        protected void AspNetPager2_PageChanged(object sender, EventArgs e)
        {
            rptDetailsBind();
        }

        protected void link_Export_Detail_Click(object sender,EventArgs e)
        {
            StringBuilder outerWhere = new StringBuilder(" 1=1");
            if (!string.IsNullOrEmpty(drp_CategoryRoot.SelectedValue))
                outerWhere.AppendFormat(" and t1.categoryParentId={0}", drp_CategoryRoot.SelectedValue);
            if (!string.IsNullOrEmpty(drp_CategorySub.SelectedValue))
                outerWhere.AppendFormat(" and t1.categoryId={0}", drp_CategorySub.SelectedValue);

            StringBuilder innerWhere = new StringBuilder(" 1=1 and [status]='4' ");
            if (ext_StartInputDt.SelectedDate != DateTime.MinValue)
                innerWhere.AppendFormat(" and DATEDIFF(d,[inputDt],'{0}')<=0", ext_StartInputDt.SelectedDate);
            if (ext_EndInputDt.SelectedDate != DateTime.MinValue)
                innerWhere.AppendFormat(" and DATEDIFF(d,[inputDt],'{0}')>=0", ext_EndInputDt.SelectedDate);
            if (drp_Company.SelectedValue.ToString() != "0")
                innerWhere.AppendFormat(" and companyId = {0}", Convert.ToInt32(drp_Company.SelectedValue));

            DateTime? startDate = null;
            DateTime? endDate = null;

            if (ext_StartInputDt.SelectedDate != DateTime.MinValue)
                startDate = ext_StartInputDt.SelectedDate;
            if (ext_EndInputDt.SelectedDate != DateTime.MinValue)
                endDate = ext_EndInputDt.SelectedDate;


            // 查询数据
            //DataTable dt = logic.orderItem.listWithPurchaseInfo(parentid, categoryid, productno, productname, startDate, endDate);
            DataTable dt = logic.orderItem.listWithPurchaseInfoOptimize(innerWhere.ToString(), outerWhere.ToString());
            dt.Columns["categoryParentName"].ColumnName = "大类";
            dt.Columns["categoryName"].ColumnName = "小类";
            dt.Columns["productNo"].ColumnName = "商品编号";
            dt.Columns["productName"].ColumnName = "商品名称";
            dt.Columns["unit"].ColumnName = "单位";
            dt.Columns["standard"].ColumnName = "规格";
            dt.Columns["sumQuantity"].ColumnName = "总数量";
            dt.Columns["sumSellerPrice"].ColumnName = "总进价";
            dt.Columns["sumAmount"].ColumnName = "总出价";
            dt.Columns["gpm"].ColumnName = "毛利";

            StringBuilder title = new StringBuilder();

            if (drp_Company.SelectedValue != "0")
                title.AppendFormat("{0} ", drp_Company.SelectedItem.Text);
            else
                title.Append("平台");

            if (ext_StartInputDt.SelectedDate != DateTime.MinValue && ext_EndInputDt.SelectedDate != DateTime.MinValue)
                title.AppendFormat("{0:yyyy-MM-dd}至{1:yyyy-MM-dd}", ext_StartInputDt.SelectedDate, ext_EndInputDt.SelectedDate);

            if (!string.IsNullOrEmpty(drp_CategorySub.SelectedValue))
                title.AppendFormat("{0}", drp_CategorySub.SelectedItem.Text);
            else if (!string.IsNullOrEmpty(drp_CategoryRoot.SelectedValue))
                title.AppendFormat("{0}", drp_CategoryRoot.SelectedItem.Text);
            else
                title.Append("平台");

            title.Append("商品销售统计");

            // 导出Excel
            ExportFacade exportInstance = new Facade.Excel.ExportFacade();
            Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
            formatOptions.Add(7, FormatStyle.ToFix2);
            formatOptions.Add(8, FormatStyle.ToFix2);
            formatOptions.Add(9, FormatStyle.ToFix2);
            formatOptions.Add(10, FormatStyle.ToFix2);

            exportInstance.ExportByWeb(dt, title.ToString(), title.ToString() + ".xls", formatOptions, title.ToString());
        }

        protected void ddlCategoryRoot_SelectedIndexChanged(object sender,EventArgs e)
        {
            if (!string.IsNullOrEmpty(ddlCategoryRoot.SelectedValue))
            {
                int categoryId = 0;
                int.TryParse(ddlCategoryRoot.SelectedValue,out categoryId);

                DataTable dt = logic.category.getByParent(categoryId);
                ddlCategorySub.DataSource = dt;
                ddlCategorySub.DataBind();
                ddlCategorySub.Items.Insert(0, new ListItem("--所有小类--", ""));
            }
        }

        // 统计分析
        protected void lin_Stat_Auto_Click(object sender, EventArgs e)
        {
            StringBuilder where = new StringBuilder("1=1");
            if (!string.IsNullOrEmpty(ddlCategoryRoot.SelectedValue))
                where.AppendFormat(" AND parentId={0}", ddlCategoryRoot.SelectedValue);
            if (!string.IsNullOrEmpty(ddlCategorySub.SelectedValue))
                where.AppendFormat(" AND categoryId={0}", ddlCategorySub.SelectedValue);
            if (txtStartOrderDt.SelectedDate != DateTime.MinValue)
                where.AppendFormat(" AND DATEDIFF(d,inputDt,'{0}')<=0", txtStartOrderDt.SelectedDate);
            if (txtEndOrderDt.SelectedDate != DateTime.MinValue)
                where.AppendFormat(" AND DATEDIFF(d,inputDt,'{0}')>=0", txtEndOrderDt.SelectedDate);

            string orderBy = string.Format("{0}",ddlFields.SelectedValue);
            int top = 50;
            int.TryParse(txtTopNum.Text,out top);

            DataTable dt = logic.product.StatQuantityCountSale(where.ToString(), orderBy, top);
            rptStat.DataSource = dt;
            rptStat.DataBind();

            ViewState["ProductStatCacheData"] = dt;
        }

        // 导出Excel
        protected void link_Export_ProductSale_Click(object sender, EventArgs e)
        {
            if (ViewState["ProductStatCacheData"]!=null)
            {
                StringBuilder title=new StringBuilder();

                if (txtStartOrderDt.SelectedDate != DateTime.MinValue && txtEndOrderDt.SelectedDate != DateTime.MinValue)
                    title.AppendFormat("{0:yyyy-MM-dd}至{1:yyyy-MM-dd}",txtStartOrderDt.SelectedDate,txtEndOrderDt.SelectedDate);

                if (!string.IsNullOrEmpty(ddlCategorySub.SelectedValue))
                    title.AppendFormat("{0}", ddlCategorySub.SelectedItem.Text);
                else if (!string.IsNullOrEmpty(ddlCategoryRoot.SelectedValue))
                    title.AppendFormat("{0}", ddlCategoryRoot.SelectedItem.Text);
                else
                    title.Append("平台");

                title.AppendFormat("{0}前{1}名商品",ddlFields.SelectedItem.Text,txtTopNum.Text);
                DataTable dt = ViewState["ProductStatCacheData"] as DataTable;
                dt.Columns[0].ColumnName = "商品名称";
                dt.Columns[1].ColumnName = "订购量";
                dt.Columns[2].ColumnName = "订购次数";
                dt.Columns[3].ColumnName = "销售总额";

                // 导出Excel
                ExportFacade exportInstance = new Facade.Excel.ExportFacade();
                Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
                formatOptions.Add(1, FormatStyle.ToFix2);
                formatOptions.Add(3, FormatStyle.ToFix2);

                exportInstance.ExportByWeb(dt, title.ToString(), title.ToString() + ".xls", formatOptions, title.ToString());
            }
        }
    }
}
